package com.kwdb.importtool.model;

import lombok.Data;

import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * SQL语句模型类
 * 表示单个SQL语句及其相关元数据
 */
@Data
public class SQLStatement {
    // 基本信息
    private String id;
    private String originalSQL;
    private String convertedSQL;
    private int lineNumber;
    private String sourceFile;

    // 语句类型
    private StatementType statementType;
    private String tableName;
    private String databaseName;

    // 转换状态
    private boolean conversionSuccess = true;
    private String conversionError;
    private List<ConversionWarning> conversionWarnings;

    // 执行状态
    private boolean executed = false;
    private boolean executionSuccess = false;
    private String executionError;
    private long executionDuration; // 毫秒
    private long executionTimestamp;

    // 元数据
    private int estimatedRowCount;
    private int actualRowCount;
    private List<String> affectedColumns;
    private List<String> dependentObjects;

    // 语法分析结果
    private SQLSyntaxTree syntaxTree;
    private List<SQLToken> tokens;

    public SQLStatement() {
        this.id = generateStatementId();
        this.conversionWarnings = new ArrayList<>();
        this.affectedColumns = new ArrayList<>();
        this.dependentObjects = new ArrayList<>();
        this.tokens = new ArrayList<>();
    }

    public SQLStatement(String originalSQL) {
        this();
        this.originalSQL = originalSQL;
        this.convertedSQL = originalSQL; // 默认使用原始SQL
        analyzeStatement();
    }

    public SQLStatement(String originalSQL, int lineNumber) {
        this(originalSQL);
        this.lineNumber = lineNumber;
    }

    /**
     * 生成语句ID
     */
    private String generateStatementId() {
        return "STMT_" + UUID.randomUUID().toString().substring(0, 8);
    }

    /**
     * 分析语句
     */
    private void analyzeStatement() {
        if (originalSQL == null || originalSQL.trim().isEmpty()) {
            this.statementType = StatementType.EMPTY;
            return;
        }

        String upperSQL = originalSQL.toUpperCase().trim();

        // 确定语句类型
        this.statementType = determineStatementType(upperSQL);

        // 提取表名和数据库名
        extractMetadata(upperSQL);

        // 简单的词法分析
        performLexicalAnalysis();
    }

    /**
     * 确定语句类型
     */
    private StatementType determineStatementType(String upperSQL) {
        if (upperSQL.startsWith("CREATE DATABASE")) {
            return StatementType.CREATE_DATABASE;
        } else if (upperSQL.startsWith("DROP DATABASE")) {
            return StatementType.DROP_DATABASE;
        } else if (upperSQL.startsWith("CREATE TABLE")) {
            return StatementType.CREATE_TABLE;
        } else if (upperSQL.startsWith("DROP TABLE")) {
            return StatementType.DROP_TABLE;
        } else if (upperSQL.startsWith("ALTER TABLE")) {
            return StatementType.ALTER_TABLE;
        } else if (upperSQL.startsWith("INSERT INTO")) {
            return StatementType.INSERT;
        } else if (upperSQL.startsWith("UPDATE")) {
            return StatementType.UPDATE;
        } else if (upperSQL.startsWith("DELETE FROM")) {
            return StatementType.DELETE;
        } else if (upperSQL.startsWith("SELECT")) {
            return StatementType.SELECT;
        } else if (upperSQL.startsWith("CREATE INDEX")) {
            return StatementType.CREATE_INDEX;
        } else if (upperSQL.startsWith("DROP INDEX")) {
            return StatementType.DROP_INDEX;
        } else if (upperSQL.startsWith("CREATE SEQUENCE")) {
            return StatementType.CREATE_SEQUENCE;
        } else if (upperSQL.startsWith("DROP SEQUENCE")) {
            return StatementType.DROP_SEQUENCE;
        } else if (upperSQL.startsWith("CREATE VIEW")) {
            return StatementType.CREATE_VIEW;
        } else if (upperSQL.startsWith("DROP VIEW")) {
            return StatementType.DROP_VIEW;
        } else if (upperSQL.startsWith("CREATE FUNCTION")) {
            return StatementType.CREATE_FUNCTION;
        } else if (upperSQL.startsWith("CREATE PROCEDURE")) {
            return StatementType.CREATE_PROCEDURE;
        } else if (upperSQL.startsWith("GRANT")) {
            return StatementType.GRANT;
        } else if (upperSQL.startsWith("REVOKE")) {
            return StatementType.REVOKE;
        } else if (upperSQL.startsWith("COMMENT ON")) {
            return StatementType.COMMENT;
        } else if (upperSQL.startsWith("USE ")) {
            return StatementType.USE;
        } else if (upperSQL.startsWith("SET ")) {
            return StatementType.SET;
        } else if (upperSQL.startsWith("BEGIN") || upperSQL.startsWith("START TRANSACTION")) {
            return StatementType.TRANSACTION_START;
        } else if (upperSQL.startsWith("COMMIT")) {
            return StatementType.TRANSACTION_COMMIT;
        } else if (upperSQL.startsWith("ROLLBACK")) {
            return StatementType.TRANSACTION_ROLLBACK;
        } else if (upperSQL.startsWith("LOCK TABLE")) {
            return StatementType.LOCK_TABLE;
        } else if (upperSQL.startsWith("UNLOCK TABLE")) {
            return StatementType.UNLOCK_TABLE;
        } else if (upperSQL.startsWith("--") || upperSQL.startsWith("#") || upperSQL.startsWith("/*")) {
            return StatementType.COMMENT;
        } else if (upperSQL.isEmpty()) {
            return StatementType.EMPTY;
        } else {
            return StatementType.OTHER;
        }
    }

    /**
     * 提取元数据
     */
    private void extractMetadata(String upperSQL) {
        try {
            switch (statementType) {
                case CREATE_DATABASE:
                case DROP_DATABASE:
                    this.databaseName = extractDatabaseName(upperSQL);
                    break;

                case CREATE_TABLE:
                case DROP_TABLE:
                case ALTER_TABLE:
                case INSERT:
                case UPDATE:
                case DELETE:
                    this.tableName = extractTableName(upperSQL);
                    break;

                case CREATE_INDEX:
                case DROP_INDEX:
                    this.tableName = extractTableNameFromIndex(upperSQL);
                    break;

                default:
                    // 其他类型可能不涉及具体表
                    break;
            }
        } catch (Exception e) {
            // 提取失败不影响主要功能
        }
    }

    /**
     * 提取数据库名
     */
    private String extractDatabaseName(String upperSQL) {
        String[] parts = upperSQL.split("\\s+");
        if (parts.length >= 3) {
            String name = parts[2];
            // 清理反引号、分号等
            return cleanIdentifier(name);
        }
        return null;
    }

    /**
     * 提取表名
     */
    private String extractTableName(String upperSQL) {
        String[] parts = upperSQL.split("\\s+");

        switch (statementType) {
            case CREATE_TABLE:
            case DROP_TABLE:
                if (parts.length >= 3) {
                    return cleanIdentifier(parts[2]);
                }
                break;

            case ALTER_TABLE:
                if (parts.length >= 3) {
                    return cleanIdentifier(parts[2]);
                }
                break;

            case INSERT:
                if (upperSQL.contains("INTO")) {
                    int intoIndex = upperSQL.indexOf("INTO");
                    String afterInto = upperSQL.substring(intoIndex + 4).trim();
                    String[] afterParts = afterInto.split("\\s+");
                    if (afterParts.length > 0) {
                        return cleanIdentifier(afterParts[0]);
                    }
                }
                break;

            case UPDATE:
                if (parts.length >= 2) {
                    return cleanIdentifier(parts[1]);
                }
                break;

            case DELETE:
                if (upperSQL.contains("FROM")) {
                    int fromIndex = upperSQL.indexOf("FROM");
                    String afterFrom = upperSQL.substring(fromIndex + 4).trim();
                    String[] afterParts = afterFrom.split("\\s+");
                    if (afterParts.length > 0) {
                        return cleanIdentifier(afterParts[0]);
                    }
                }
                break;
        }

        return null;
    }

    /**
     * 从索引语句提取表名
     */
    private String extractTableNameFromIndex(String upperSQL) {
        if (upperSQL.contains("ON")) {
            int onIndex = upperSQL.indexOf("ON");
            String afterOn = upperSQL.substring(onIndex + 2).trim();
            String[] afterParts = afterOn.split("\\s+");
            if (afterParts.length > 0) {
                return cleanIdentifier(afterParts[0]);
            }
        }
        return null;
    }

    /**
     * 清理标识符
     */
    private String cleanIdentifier(String identifier) {
        if (identifier == null) return null;

        // 移除反引号、引号、分号等
        return identifier.replace("`", "")
                .replace("\"", "")
                .replace("'", "")
                .replace(";", "")
                .trim();
    }

    /**
     * 执行词法分析
     */
    private void performLexicalAnalysis() {
        if (originalSQL == null) return;

        String sql = originalSQL;
        int pos = 0;
        int length = sql.length();

        while (pos < length) {
            char c = sql.charAt(pos);

            if (Character.isWhitespace(c)) {
                // 跳过空白字符
                pos++;
                continue;
            }

            if (c == '-' && pos + 1 < length && sql.charAt(pos + 1) == '-') {
                // 单行注释
                int end = sql.indexOf('\n', pos);
                if (end == -1) end = length;
                tokens.add(new SQLToken(TokenType.COMMENT, sql.substring(pos, end), pos));
                pos = end;
                continue;
            }

            if (c == '/' && pos + 1 < length && sql.charAt(pos + 1) == '*') {
                // 多行注释
                int end = sql.indexOf("*/", pos);
                if (end == -1) end = length;
                tokens.add(new SQLToken(TokenType.COMMENT, sql.substring(pos, end + 2), pos));
                pos = end + 2;
                continue;
            }

            if (c == '\'' || c == '"' || c == '`') {
                // 字符串或标识符
                char quote = c;
                int start = pos;
                pos++; // 跳过开始引号

                while (pos < length) {
                    if (sql.charAt(pos) == quote) {
                        if (pos + 1 < length && sql.charAt(pos + 1) == quote) {
                            // 转义引号
                            pos += 2;
                        } else {
                            break;
                        }
                    } else {
                        pos++;
                    }
                }

                if (pos < length) {
                    pos++; // 跳过结束引号
                }

                String value = sql.substring(start, pos);
                TokenType type = (quote == '`') ? TokenType.IDENTIFIER : TokenType.STRING;
                tokens.add(new SQLToken(type, value, start));
                continue;
            }

            if (Character.isLetter(c) || c == '_') {
                // 关键字或标识符
                int start = pos;
                while (pos < length && (Character.isLetterOrDigit(sql.charAt(pos)) || sql.charAt(pos) == '_')) {
                    pos++;
                }

                String value = sql.substring(start, pos);
                TokenType type = isKeyword(value) ? TokenType.KEYWORD : TokenType.IDENTIFIER;
                tokens.add(new SQLToken(type, value, start));
                continue;
            }

            if (Character.isDigit(c)) {
                // 数字
                int start = pos;
                while (pos < length && (Character.isDigit(sql.charAt(pos)) || sql.charAt(pos) == '.')) {
                    pos++;
                }

                tokens.add(new SQLToken(TokenType.NUMBER, sql.substring(start, pos), start));
                continue;
            }

            // 操作符和标点符号
            tokens.add(new SQLToken(TokenType.OPERATOR, String.valueOf(c), pos));
            pos++;
        }
    }

    /**
     * 判断是否为关键字
     */
    private boolean isKeyword(String word) {
        String upperWord = word.toUpperCase();
        return upperWord.equals("SELECT") || upperWord.equals("FROM") || upperWord.equals("WHERE") ||
                upperWord.equals("INSERT") || upperWord.equals("INTO") || upperWord.equals("VALUES") ||
                upperWord.equals("UPDATE") || upperWord.equals("SET") || upperWord.equals("DELETE") ||
                upperWord.equals("CREATE") || upperWord.equals("TABLE") || upperWord.equals("DATABASE") ||
                upperWord.equals("ALTER") || upperWord.equals("DROP") || upperWord.equals("INDEX") ||
                upperWord.equals("UNIQUE") || upperWord.equals("PRIMARY") || upperWord.equals("KEY") ||
                upperWord.equals("FOREIGN") || upperWord.equals("REFERENCES") || upperWord.equals("CONSTRAINT") ||
                upperWord.equals("AND") || upperWord.equals("OR") || upperWord.equals("NOT") ||
                upperWord.equals("NULL") || upperWord.equals("DEFAULT") || upperWord.equals("AUTO_INCREMENT");
    }

    /**
     * 添加转换警告
     */
    public void addConversionWarning(String warning) {
        this.conversionWarnings.add(new ConversionWarning(warning));
    }

    /**
     * 添加转换警告（带详细信息）
     */
    public void addConversionWarning(String warning, String details) {
        this.conversionWarnings.add(new ConversionWarning(warning, details));
    }

    /**
     * 标记执行成功
     */
    public void markExecutionSuccess(long duration) {
        this.executed = true;
        this.executionSuccess = true;
        this.executionDuration = duration;
        this.executionTimestamp = System.currentTimeMillis();
    }

    /**
     * 标记执行失败
     */
    public void markExecutionFailure(String error, long duration) {
        this.executed = true;
        this.executionSuccess = false;
        this.executionError = error;
        this.executionDuration = duration;
        this.executionTimestamp = System.currentTimeMillis();
    }

    /**
     * 获取SQL语句的简化表示（用于日志）
     */
    public String getSimplifiedSQL() {
        if (originalSQL == null) return null;

        String sql = originalSQL.trim();
        if (sql.length() > 100) {
            return sql.substring(0, 100) + "...";
        }
        return sql;
    }

    /**
     * 判断是否为DDL语句
     */
    public boolean isDDL() {
        return statementType == StatementType.CREATE_DATABASE ||
                statementType == StatementType.CREATE_TABLE ||
                statementType == StatementType.CREATE_INDEX ||
                statementType == StatementType.CREATE_SEQUENCE ||
                statementType == StatementType.CREATE_VIEW ||
                statementType == StatementType.CREATE_FUNCTION ||
                statementType == StatementType.CREATE_PROCEDURE ||
                statementType == StatementType.ALTER_TABLE ||
                statementType == StatementType.DROP_DATABASE ||
                statementType == StatementType.DROP_TABLE ||
                statementType == StatementType.DROP_INDEX ||
                statementType == StatementType.DROP_SEQUENCE ||
                statementType == StatementType.DROP_VIEW;
    }

    /**
     * 判断是否为DML语句
     */
    public boolean isDML() {
        return statementType == StatementType.INSERT ||
                statementType == StatementType.UPDATE ||
                statementType == StatementType.DELETE ||
                statementType == StatementType.SELECT;
    }

    /**
     * 判断是否为数据定义语句
     */
    public boolean isDataDefinition() {
        return isDDL() || statementType == StatementType.GRANT ||
                statementType == StatementType.REVOKE ||
                statementType == StatementType.COMMENT;
    }

    /**
     * 判断是否为事务控制语句
     */
    public boolean isTransactionControl() {
        return statementType == StatementType.TRANSACTION_START ||
                statementType == StatementType.TRANSACTION_COMMIT ||
                statementType == StatementType.TRANSACTION_ROLLBACK;
    }

    /**
     * 语句类型枚举
     */
    public enum StatementType {
        // 数据库操作
        CREATE_DATABASE,
        DROP_DATABASE,
        USE,

        // 表操作
        CREATE_TABLE,
        DROP_TABLE,
        ALTER_TABLE,

        // 数据操作
        INSERT,
        UPDATE,
        DELETE,
        SELECT,

        // 索引操作
        CREATE_INDEX,
        DROP_INDEX,

        // 序列操作
        CREATE_SEQUENCE,
        DROP_SEQUENCE,

        // 视图操作
        CREATE_VIEW,
        DROP_VIEW,

        // 函数和存储过程
        CREATE_FUNCTION,
        CREATE_PROCEDURE,

        // 权限操作
        GRANT,
        REVOKE,

        // 注释
        COMMENT,

        // 配置
        SET,

        // 事务控制
        TRANSACTION_START,
        TRANSACTION_COMMIT,
        TRANSACTION_ROLLBACK,

        // 锁操作
        LOCK_TABLE,
        UNLOCK_TABLE,

        // 其他
        EMPTY,
        OTHER
    }

    /**
     * 词法标记类型
     */
    public enum TokenType {
        KEYWORD,
        IDENTIFIER,
        STRING,
        NUMBER,
        OPERATOR,
        COMMENT,
        PUNCTUATION
    }

    /**
     * SQL词法标记
     */
    @Data
    public static class SQLToken {
        private final TokenType type;
        private final String value;
        private final int position;

        public SQLToken(TokenType type, String value, int position) {
            this.type = type;
            this.value = value;
            this.position = position;
        }
    }

    /**
     * 转换警告
     */
    @Data
    public static class ConversionWarning {
        private String id;
        private String warning;
        private String details;
        private long timestamp;

        public ConversionWarning(String warning) {
            this(warning, null);
        }

        public ConversionWarning(String warning, String details) {
            this.id = "WARN_" + UUID.randomUUID().toString().substring(0, 8);
            this.warning = warning;
            this.details = details;
            this.timestamp = System.currentTimeMillis();
        }
    }

    /**
     * SQL语法树（简化版）
     */
    @Data
    public static class SQLSyntaxTree {
        private Node root;
        private List<Node> nodes;

        public SQLSyntaxTree() {
            this.nodes = new ArrayList<>();
        }

        @Data
        public static class Node {
            private String type;
            private String value;
            private List<Node> children;

            public Node(String type, String value) {
                this.type = type;
                this.value = value;
                this.children = new ArrayList<>();
            }

            public void addChild(Node child) {
                this.children.add(child);
            }
        }
    }

    @Override
    public String toString() {
        return String.format("SQLStatement[type=%s, table=%s, line=%d, sql=%s]",
                statementType, tableName, lineNumber, getSimplifiedSQL());
    }
}